package com.chenfei.poi.utils;

import com.chenfei.poi.domain.DeviceCirculation;
import com.chenfei.poi.response.device.DeviceDetailResp;
import org.apache.poi.hpsf.DocumentSummaryInformation;
import org.apache.poi.hpsf.SummaryInformation;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.springframework.http.HttpHeaders;
import org.springframework.http.HttpStatus;
import org.springframework.http.MediaType;
import org.springframework.http.ResponseEntity;
import org.springframework.util.StringUtils;
import org.springframework.web.multipart.MultipartFile;

import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * @author chenfei
 * @description
 * @since 2018/2/5
 */
public class PoiUtils {

    private static SimpleDateFormat sfd = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

    public static List<DeviceCirculation> importEmp2List(MultipartFile file, Integer channelId) {
        List<DeviceCirculation> devices = new ArrayList<>();
        try {
            HSSFWorkbook workbook = new HSSFWorkbook(new POIFSFileSystem(file.getInputStream()));
            int numberOfSheets = workbook.getNumberOfSheets();
            for (int i = 0; i < numberOfSheets; i++) {
                HSSFSheet sheet = workbook.getSheetAt(i);
                int physicalNumberOfRows = sheet.getPhysicalNumberOfRows();
                DeviceCirculation deviceCirculation;
                for (int j = 0; j < physicalNumberOfRows; j++) {
                    if (j == 0) {
                        continue;//标题行
                    }
                    HSSFRow row = sheet.getRow(j);
                    if (row == null) {
                        continue;//没数据
                    }
                    int physicalNumberOfCells = row.getPhysicalNumberOfCells();
                    deviceCirculation = new DeviceCirculation();
                    for (int k = 0; k < physicalNumberOfCells; k++) {
                        HSSFCell cell = row.getCell(k);
                        String cellValue = cell.getStringCellValue();
                        if (cellValue == null) {
                            cellValue = "";
                        }
                        deviceCirculation.setMac(cellValue);
                        deviceCirculation.setFirstChannelId(channelId);
                        deviceCirculation.setFirstTime(new Date());
                        deviceCirculation.setCreateTime(new Date());
                        devices.add(deviceCirculation);
                    }
                }
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
        return devices;
    }

    public static List<DeviceCirculation> importSpreadMac2List(MultipartFile file, Integer channelId) {
        List<DeviceCirculation> devices = new ArrayList<>();
        try {
            HSSFWorkbook workbook = new HSSFWorkbook(new POIFSFileSystem(file.getInputStream()));
            int numberOfSheets = workbook.getNumberOfSheets();
            for (int i = 0; i < numberOfSheets; i++) {
                HSSFSheet sheet = workbook.getSheetAt(i);
                int physicalNumberOfRows = sheet.getPhysicalNumberOfRows();
                DeviceCirculation deviceCirculation;
                for (int j = 0; j < physicalNumberOfRows; j++) {
                    if (j == 0) {
                        continue;//标题行
                    }
                    HSSFRow row = sheet.getRow(j);
                    if (row == null) {
                        continue;//没数据
                    }
                    int physicalNumberOfCells = row.getPhysicalNumberOfCells();
                    deviceCirculation = new DeviceCirculation();
                    for (int k = 0; k < physicalNumberOfCells; k++) {
                        HSSFCell cell = row.getCell(k);
                        String cellValue = cell.getStringCellValue();
                        if (cellValue == null) {
                            cellValue = "";
                        }
                        deviceCirculation.setMac(cellValue);
                        deviceCirculation.setFirstChannelId(channelId);
                        deviceCirculation.setFirstTime(new Date());
                        deviceCirculation.setCreateTime(new Date());
                        devices.add(deviceCirculation);
                    }
                }
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
        return devices;
    }

    public static ResponseEntity<byte[]> exportEmp2Excel(List<DeviceDetailResp> deviceDetailResps) {
        HttpHeaders headers = null;
        ByteArrayOutputStream baos = null;
        try {
            HSSFWorkbook workbook = getHSSFWorkbook();
            //创建Excel表单
            HSSFSheet sheet = workbook.createSheet("沃联集团设备信息表");
            //创建标题的显示样式
            HSSFCellStyle headerStyle = workbook.createCellStyle();
            headerStyle.setFillForegroundColor(IndexedColors.YELLOW.index);
            headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            //定义列的宽度
            sheet.setColumnWidth(0, 5 * 256);
            sheet.setColumnWidth(1, 12 * 256);
            sheet.setColumnWidth(2, 10 * 256);
            sheet.setColumnWidth(3, 5 * 256);
            //第四列(出生日期那一列)的宽度为10个字符的宽度
            sheet.setColumnWidth(4, 12 * 256);
            sheet.setColumnWidth(5, 20 * 256);
            sheet.setColumnWidth(6, 10 * 256);
            //5.设置表头
            HSSFRow headerRow = sheet.createRow(0);
            HSSFCell cell0 = headerRow.createCell(0);
            cell0.setCellValue("MAC");
            cell0.setCellStyle(headerStyle);
            HSSFCell cell1 = headerRow.createCell(1);
            cell1.setCellValue("发货渠道");
            cell1.setCellStyle(headerStyle);
            HSSFCell cell2 = headerRow.createCell(2);
            cell2.setCellValue("发货日期");
            cell2.setCellStyle(headerStyle);
            HSSFCell cell3 = headerRow.createCell(3);
            cell3.setCellValue("所属商户");
            cell3.setCellStyle(headerStyle);
            HSSFCell cell4 = headerRow.createCell(4);
            cell4.setCellValue("发货商户时间");
            cell4.setCellStyle(headerStyle);
            HSSFCell cell5 = headerRow.createCell(5);
            cell5.setCellValue("设备状态");
            cell5.setCellStyle(headerStyle);
            HSSFCell cell6 = headerRow.createCell(6);
            cell6.setCellValue("激活日期");
            cell6.setCellStyle(headerStyle);

            //6.装数据
            for (int i = 0; i < deviceDetailResps.size(); i++) {
                HSSFRow row = sheet.createRow(i + 1);
                DeviceDetailResp deviceDetailResp = deviceDetailResps.get(i);
                row.createCell(0).setCellValue(deviceDetailResp.getMac());
                row.createCell(1).setCellValue(deviceDetailResp.getFirstChannelName());
                row.createCell(2).setCellValue(deviceDetailResp.getFirstTime());
                if(StringUtils.isEmpty(deviceDetailResp.getBusinessName())){
                    row.createCell(3).setCellValue("");
                    row.createCell(4).setCellValue("");
                }else{
                    row.createCell(3).setCellValue(deviceDetailResp.getBusinessName());
                    row.createCell(4).setCellValue(deviceDetailResp.getBusinessTime());
                }
                row.createCell(5).setCellValue(deviceDetailResp.getOnlineStatus());
                if(deviceDetailResp.getActiveTime() == null){
                    row.createCell(3).setCellValue("");
                }else{
                    row.createCell(6).setCellValue(deviceDetailResp.getActiveTime());
                }
            }
            headers = new HttpHeaders();
            headers.setContentDispositionFormData("attachment", new String("设备表.xls".getBytes("UTF-8"), "iso-8859-1"));
            headers.setContentType(MediaType.APPLICATION_OCTET_STREAM);
            baos = new ByteArrayOutputStream();
            workbook.write(baos);
        } catch (IOException e) {
            e.printStackTrace();
        }
        return new ResponseEntity<byte[]>(baos.toByteArray(), headers, HttpStatus.CREATED);
    }

    private static HSSFWorkbook getHSSFWorkbook() throws IOException{
        //1.创建Excel文档
        HSSFWorkbook workbook = new HSSFWorkbook();
        //2.创建文档摘要
        workbook.createInformationProperties();
        //3.获取文档信息，并配置
        DocumentSummaryInformation dsi = workbook.getDocumentSummaryInformation();
        //3.1文档类别
        dsi.setCategory("设备信息");
        //3.2设置文档管理员
        dsi.setManager("江南一点雨");
        //3.3设置组织机构
        dsi.setCompany("沃联集团");
        //4.获取摘要信息并配置
        SummaryInformation si = workbook.getSummaryInformation();
        //4.1设置文档主题
        si.setSubject("设备信息表");
        //4.2.设置文档标题
        si.setTitle("设备信息");
        //4.3 设置文档作者
        si.setAuthor("沃联集团");
        //4.4设置文档备注
        si.setComments("备注信息暂无");

        return workbook;
    }
}
